from odoo import api, fields, models, _
import io
import base64
import unicodedata
from odoo.exceptions import UserError
from collections import defaultdict
try:
    import xlsxwriter
except Exception:  # pragma: no cover
    xlsxwriter = None


class DocumentsPOSummaryWizard(models.TransientModel):
    _name = "documents.po_summary.wizard"
    _description = "采购订单汇总向导"

    language_mode = fields.Selection(
        selection=[("zh", "中文"), ("en", "英文"), ("it", "意大利文")],
        string="语言",
        default="zh",
        required=True,
    )
    export_format = fields.Selection(
        selection=[("xlsx", "XLSX")],
        string="导出格式",
        default="xlsx",
        required=True,
    )
    report_type = fields.Selection(
        selection=[("summary", "汇总"), ("detailed", "明细")],
        string="报告类型",
        default="summary",
        required=True,
    )
    date = fields.Date(string="日期", default=fields.Date.context_today, required=True)
    company_id = fields.Many2one(
        comodel_name="res.company",
        string="公司",
        default=lambda self: self.env.company,
        required=True,
    )
    purchase_ids = fields.Many2many("purchase.order", string="采购订单", required=True)
    note = fields.Text(string="备注")

    # Generated file for download
    file_name = fields.Char(string="文件名", readonly=True)
    file_data = fields.Binary(string="文件", readonly=True)

    @api.onchange("purchase_ids")
    def _onchange_purchase_ids(self):
        if len(self.purchase_ids) == 1:
            order = self.purchase_ids[:1]
            if order.date_order:
                try:
                    export_date = fields.Datetime.context_timestamp(self, order.date_order).date()
                except Exception:
                    export_date = fields.Date.to_date(order.date_order)
                self.date = export_date

    # ------------------------- Helpers -------------------------
    def _i18n_labels(self, lang):
        labels = {
            "zh": {
                "title": "采购单汇总",
                "date": "采购单日期",
                "number": "编号",
                "seq": "序号",
                "product": "产品",
                "desc": "描述",
                "qty": "数量",
                "total_qty": "总数量",
                "company": "公司",
                "supplier": "供应商",
                "buyer": "采购员",
                "purchase_orders": "采购单",
                "customer_ref": "客户参考",
                "po_number": "采购单号",
                "uom": "单位",
                "unit_price": "单价",
                "tax": "税",
                "amount": "金额",
                "subtotal": "未税金额",
                "tax_amount": "税额",
                "total_with_tax": "总计",
            },
            "en": {
                "title": "Purchase Order Summary",
                "date": "Purchase Order Date",
                "number": "Number",
                "seq": "Seq",
                "product": "Product",
                "desc": "Description",
                "qty": "Quantity",
                "total_qty": "Total Quantity",
                "company": "Company",
                "supplier": "Supplier",
                "buyer": "Buyer",
                "purchase_orders": "Purchase Orders",
                "customer_ref": "Customer Reference",
                "po_number": "PO Number",
                "uom": "UoM",
                "unit_price": "Unit Price",
                "tax": "Tax",
                "amount": "Total (incl.)",
                "subtotal": "Subtotal (excl.)",
                "tax_amount": "Tax",
                "total_with_tax": "Total (incl.)",
            },
            "it": {
                "title": "Riepilogo Ordine d'Acquisto",
                "date": "Data Ordine",
                "number": "Numero",
                "seq": "N°",
                "product": "Prodotto",
                "desc": "Descrizione",
                "qty": "Quantità",
                "total_qty": "Quantità totale",
                "company": "Azienda",
                "supplier": "Fornitore",
                "buyer": "Acquirente",
                "purchase_orders": "Ordini di Acquisto",
                "customer_ref": "Riferimento Cliente",
                "po_number": "Numero Ordine",
                "uom": "UdM",
                "unit_price": "Prezzo Unitario",
                "tax": "Imposta",
                "amount": "Totale (incl.)",
                "subtotal": "Subtotale (escl.)",
                "tax_amount": "Imposta",
                "total_with_tax": "Totale (incl.)",
            },
        }
        return labels.get(lang or "zh")

    def _display_width(self, text):
        s = str(text or "")
        width = 0
        for ch in s:
            eaw = unicodedata.east_asian_width(ch)
            width += 2 if eaw in ("F", "W") else 1
        return width

    def _map_to_odoo_lang(self, lang_code):
        mapping = {
            "zh": "zh_CN",
            "en": "en_US",
            "it": "it_IT",
        }
        return mapping.get(lang_code, "zh_CN")

    def _get_valid_odoo_lang(self, lang_code):
        """Return lang_code only if installed in this DB; else return None."""
        if not lang_code:
            return None
        Lang = self.env["res.lang"]
        rec = Lang.search([("code", "=", lang_code)], limit=1)
        return rec.code if rec else None

    def _merge_purchase_lines(self, purchase_lines):
        """合并相同默认编号、名称及单价的采购行"""
        merged_data = defaultdict(lambda: {"qty": 0.0, "lines": []})
        
        for line in purchase_lines:
            # 使用默认编号 + 产品名称 + 单价 作为合并键
            product_code = line.product_id.default_code or ""
            product_name = line.product_id.name or ""
            price_unit_key = round(line.price_unit or 0.0, 6)
            key = (product_code, product_name, price_unit_key)
            merged_data[key]["qty"] += line.product_qty
            merged_data[key]["lines"].append(line)
        
        # 转换为列表格式
        merged_lines = []
        for (product_code, product_name, _price_unit), data in merged_data.items():
            merged_lines.append({
                "product_code": product_code,
                "product_name": product_name,
                "total_qty": data["qty"],
                "sample_line": data["lines"][0],  # 用于获取其他信息
                "lines": data["lines"],
            })
        
        return merged_lines

    def _merge_purchase_lines_by_code_name(self, purchase_lines):
        """合并相同默认编号与产品名称（忽略单价）的采购行"""
        merged_data = defaultdict(lambda: {"qty": 0.0, "lines": []})
        for line in purchase_lines:
            product_code = line.product_id.default_code or ""
            product_name = line.product_id.name or ""
            key = (product_code, product_name)
            merged_data[key]["qty"] += line.product_qty
            merged_data[key]["lines"].append(line)
        merged_lines = []
        for (product_code, product_name), data in merged_data.items():
            merged_lines.append({
                "product_code": product_code,
                "product_name": product_name,
                "total_qty": data["qty"],
                "sample_line": data["lines"][0],
            })
        return merged_lines

    def _get_customer_refs(self, orders):
        """从采购单本身读取客户参考字段 `purchase.order.customer_reference` 并去重。"""
        refs = []
        for order in orders:
            ref = (order.customer_reference or "").strip()
            if ref:
                refs.append(ref)
        # 唯一化且保持顺序
        seen = set()
        unique_refs = []
        for r in refs:
            if r not in seen:
                seen.add(r)
                unique_refs.append(r)
        return unique_refs

    # ------------------------- Export -------------------------
    def action_export(self):
        self.ensure_one()
        if self.export_format != "xlsx":
            return {"type": "ir.actions.act_window_close"}
        if not xlsxwriter:
            raise UserError(_("The Python package 'xlsxwriter' is required to export XLSX files."))

        orders = self.purchase_ids
        if not orders:
            raise UserError(_("Please select a purchase order."))

        # 校验供应商一致
        vendor_ids = orders.mapped("partner_id").ids
        if len(set(vendor_ids)) > 1:
            raise UserError(_("请选择相同供应商的采购订单进行汇总导出。"))
        order = orders[0]

        lang = self.language_mode or "zh"
        L = self._i18n_labels(lang)
        odoo_lang = self._get_valid_odoo_lang(self._map_to_odoo_lang(lang))

        # Lines (skip sections/notes)
        all_lines = orders.mapped("order_line").filtered(lambda l: not l.display_type)

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {"in_memory": True})
        sheet = workbook.add_worksheet(L["title"])  # localized sheet name
        
        # 打印设置：A4 纵向，按页宽缩放，水平居中，边距
        try:
            sheet.set_portrait()
            sheet.set_paper(9)  # A4
            sheet.fit_to_pages(1, 0)
            sheet.center_horizontally()
            sheet.set_margins(left=0.3, right=0.3, top=0.6, bottom=0.8)
        except Exception:
            pass

        # Styles
        fmt_title = workbook.add_format({"bold": True, "font_size": 20, "align": "center", "valign": "vcenter"})
        fmt_subtitle = workbook.add_format({"bold": True, "font_size": 12})
        fmt_header = workbook.add_format({"bold": True, "align": "center", "valign": "vcenter", "border": 1})
        fmt_cell = workbook.add_format({"align": "left", "valign": "vcenter", "border": 1})
        fmt_cell_center = workbook.add_format({"align": "center", "valign": "vcenter", "border": 1})
        fmt_cell_left = workbook.add_format({"align": "left", "valign": "vcenter"})
        fmt_number = workbook.add_format({"align": "right", "valign": "vcenter", "border": 1, "num_format": "#,##0.00"})
        fmt_number_noborder = workbook.add_format({"align": "right", "valign": "vcenter", "num_format": "#,##0.00"})
        fmt_tot_label = workbook.add_format({"align": "right", "bold": True})
        fmt_note = workbook.add_format({"italic": True})

        # Header / Title
        if self.report_type == "summary":
            sheet.merge_range(0, 0, 0, 3, L["title"], fmt_title)
        else:
            sheet.merge_range(0, 0, 0, 7, L["title"], fmt_title)
        row = 1
        
        # Company and Supplier
        company_name = (self.company_id.display_name or self.company_id.name or "")
        supplier_name = (order.partner_id.display_name or order.partner_id.name or "")
        right_col = 2 if self.report_type == "summary" else 4
        sheet.write(row, 0, f"{L['company']}: {company_name}")
        sheet.write(row, right_col, f"{L['supplier']}: {supplier_name}")
        row += 1
        
        # Date and Buyer (if单个订单显示采购员，否则留空)
        buyers = [o.user_id and o.user_id.name or "" for o in orders]
        buyer_display = buyers[0] if len(set(buyers)) == 1 else ""
        if len(orders) == 1 and orders[0].date_order:
            try:
                export_date = fields.Datetime.context_timestamp(self, orders[0].date_order).date()
            except Exception:
                export_date = fields.Date.to_date(orders[0].date_order)
        else:
            export_date = self.date
        sheet.write(row, 0, f"{L['date']}: {fields.Date.to_string(export_date)}")
        sheet.write(row, right_col, f"{L['buyer']}: {buyer_display}")
        row += 1

        # Purchase Orders (客户参考在详细版改为单独一列，不在此处显示)
        po_names = ", ".join(orders.mapped("name"))
        sheet.write(row, 0, f"{L['purchase_orders']}: {po_names}")
        if self.report_type != "detailed":
            customer_refs = ", ".join(self._get_customer_refs(orders))
            sheet.write(row, right_col, f"{L['customer_ref']}: {customer_refs}")
        row += 2

        if self.report_type == "summary":
            # Summary: merge same products (code + name) and show total qty
            merged_lines = self._merge_purchase_lines_by_code_name(all_lines)

            # Column widths (dynamic based on headers and data)
            headers = [L["seq"], L["number"], L["desc"], L["qty"]]
            base_widths = [6, 18, 36, 12]
            caps = [24, 28, 60, 14]
            header_widths = [max(base_widths[i], self._display_width(h) + 2) for i, h in enumerate(headers)]

            max_idx_w = self._display_width(str(max(1, len(merged_lines)))) + 2
            max_code_w = 0
            max_desc_w = 0
            max_qty_w = 0
            for item in merged_lines:
                product = item["sample_line"].product_id
                product_code = product.default_code or ""
                if odoo_lang:
                    desc = product.with_context(lang=odoo_lang).name or item["product_name"]
                else:
                    desc = item["product_name"]
                qty = item["total_qty"]
                max_code_w = max(max_code_w, self._display_width(product_code) + 2)
                max_desc_w = max(max_desc_w, self._display_width(desc) + 2)
                max_qty_w = max(max_qty_w, self._display_width(f"{qty:,.2f}") + 2)

            data_widths = [max_idx_w, max_code_w, max_desc_w, max_qty_w]
            data_widths[0] = max(data_widths[0], self._display_width(f"{L['total_qty']}:") + 2)
            final_widths = [min(max(header_widths[i], data_widths[i], base_widths[i]), caps[i]) for i in range(4)]
            for col_idx, width in enumerate(final_widths):
                col_letter = chr(ord("A") + col_idx)
                sheet.set_column(f"{col_letter}:{col_letter}", width)

            # Table headers
            start_table_row = row
            for col, head in enumerate(headers):
                sheet.write(start_table_row, col, head, fmt_header)
            row = start_table_row + 1

            # Body
            total_qty = 0.0
            for idx, item in enumerate(merged_lines, start=1):
                product_code = item["product_code"]
                if odoo_lang:
                    description = item["sample_line"].product_id.with_context(lang=odoo_lang).name or item["product_name"]
                else:
                    description = item["product_name"]
                qty = item["total_qty"]
                total_qty += qty
                sheet.write(row, 0, idx, fmt_cell_center)
                sheet.write(row, 1, product_code, fmt_cell)
                sheet.write(row, 2, description, fmt_cell)
                sheet.write_number(row, 3, qty, fmt_cell_center)
                row += 1

            # Total quantity
            row += 1
            sheet.merge_range(row, 0, row, 2, f"{L['total_qty']}:", fmt_tot_label)
            sheet.write_number(row, 3, total_qty, fmt_cell_left)
        else:
            # Detailed: merge identical products (default_code + name + unit price) and show qty, uom, unit price, tax, total (incl.)
            merged_lines = self._merge_purchase_lines(all_lines)
            headers = [
                L["seq"], L["number"], L["desc"], L["qty"], L["customer_ref"],
                L["unit_price"], L["tax"], L["amount"],
            ]
            # Set widths (UoM 列移除，新增客户参考列)
            widths = [6, 16, 36, 10, 24, 14, 14, 18]
            for col_idx, width in enumerate(widths):
                col_letter = chr(ord("A") + col_idx)
                sheet.set_column(f"{col_letter}:{col_letter}", width)

            start_table_row = row
            for col, head in enumerate(headers):
                sheet.write(start_table_row, col, head, fmt_header)
            row = start_table_row + 1

            grand_subtotal = 0.0
            grand_total = 0.0

            for idx, item in enumerate(merged_lines, start=1):
                sample_line = item["sample_line"]
                product = sample_line.product_id
                product_code = item["product_code"]
                description = product.with_context(lang=odoo_lang).name if odoo_lang else (item["product_name"] or sample_line.name)
                qty = item["total_qty"]
                price_unit = sample_line.price_unit or 0.0
                currency = sample_line.order_id.currency_id
                taxes = sample_line.taxes_id
                tax_names = ", ".join(taxes.mapped("name")) if taxes else ""
                # 客户参考：从本组关联的采购订单找到其来源的销售单客户参考并去重
                related_orders = self.env["purchase.order"].browse(list({l.order_id.id for l in item["lines"]}))
                customer_refs_list = self._get_customer_refs(related_orders)
                customer_refs_text = ", ".join(customer_refs_list)

                # Compute untaxed subtotal and total including tax for the grouped lines
                merged_subtotal = 0.0
                merged_total_included = 0.0
                for line in item["lines"]:
                    line_taxes = line.taxes_id
                    line_currency = line.order_id.currency_id
                    line_qty = line.product_qty
                    line_price = line.price_unit or 0.0
                    if line_taxes:
                        res = line_taxes.compute_all(line_price, line_currency, line_qty, product=line.product_id, partner=line.order_id.partner_id)
                        merged_subtotal += res.get("total_excluded", line_price * line_qty)
                        merged_total_included += res.get("total_included", line_price * line_qty)
                    else:
                        merged_subtotal += line_price * line_qty
                        merged_total_included += line_price * line_qty

                grand_subtotal += merged_subtotal
                grand_total += merged_total_included

                sheet.write_number(row, 0, idx, fmt_cell_center)
                sheet.write(row, 1, product_code, fmt_cell)
                sheet.write(row, 2, description, fmt_cell)
                sheet.write_number(row, 3, qty, fmt_cell_center)
                sheet.write(row, 4, customer_refs_text, fmt_cell)
                sheet.write(row, 5, f"{currency.symbol or ''}{price_unit:,.2f}", fmt_cell)
                sheet.write(row, 6, tax_names, fmt_cell)
                sheet.write(row, 7, f"{currency.symbol or ''}{merged_subtotal:,.2f}", fmt_number)
                row += 1

            # Footer: subtotal (untaxed), tax amount, grand total
            row += 1
            sheet.merge_range(row, 0, row, 6, L["subtotal"], fmt_tot_label)
            sheet.write(row, 7, grand_subtotal, fmt_number_noborder)
            row += 1
            sheet.merge_range(row, 0, row, 6, L["tax_amount"], fmt_tot_label)
            sheet.write(row, 7, grand_total - grand_subtotal, fmt_number_noborder)
            row += 1
            sheet.merge_range(row, 0, row, 6, L["total_with_tax"], fmt_tot_label)
            sheet.write(row, 7, grand_total, fmt_number_noborder)

        if self.note:
            sheet.write(row + 2, 0, self.note, fmt_note)

        workbook.close()
        output.seek(0)
        data = output.read()

        # Filename
        po_part = "+".join(orders.mapped("name"))
        if len(po_part) > 60:
            po_part = f"{orders[0].name}+...+{orders[-1].name}"
        filename = f"{L['title']}_{po_part}.xlsx"
        self.write({
            "file_name": filename,
            "file_data": base64.b64encode(data),
        })

        return {
            "type": "ir.actions.act_url",
            "url": (
                f"/web/content/?model={self._name}&id={self.id}"
                f"&field=file_data&filename_field=file_name&download=true"
            ),
            "target": "self",
        } 